﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace BackOffice
{
    public class Report
    {
        public static DataTable MemberReport1(string member, string email, string dateFrom, string dateTo, int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "MemberReport1";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Member", SqlDbType.VarChar, 50));
                cmd.Parameters["@Member"].Value = member;

                cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 100));
                cmd.Parameters["@Email"].Value = email;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = dateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = dateTo;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }


        public static DataTable VendorReport1(string member, string email, string registerFrom, string registerTo, string dateFrom, string dateTo, int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "VendorReport1";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = member;

                cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 100));
                cmd.Parameters["@Email"].Value = email;

                cmd.Parameters.Add(new SqlParameter("@RegisterFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@RegisterFrom"].Value = registerFrom;

                cmd.Parameters.Add(new SqlParameter("@RegisterTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@RegisterTo"].Value = registerTo;
                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = dateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = dateTo;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable MemberSalesReport1(string name, string paidFrom, string paidTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "MemberSalesReport1";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@PaidFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@PaidFrom"].Value = paidFrom;

                cmd.Parameters.Add(new SqlParameter("@PaidTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@PaidTo"].Value = paidTo;
                
                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable showOrderDetails(int orderID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showOrderDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.Int));
                cmd.Parameters["@OrderID"].Value = orderID;                

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable Survey_Report1(string vendor, string dateFrom, string dateTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "Survey_Report1";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = vendor;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = dateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = dateTo;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable Survey_Report2(string vendor, string dateFrom, string dateTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "Survey_Report2";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = vendor;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = dateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = dateTo;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }        
    }
}